//

const fs = require("fs");
const path = require("path");
const mysql = require("mysql");
const dayjs = require("dayjs");
const XLSX = require("xlsx");

const handleQuery = async (query) => {
  const host = "localhost";
  const user = "cdapp";
  const password = "gzzxjjwt1";
  const database = "cdapp";
  // 创建数据库连接
  const connection = mysql.createConnection({ host, user, password, database });
  return new Promise((resolve, reject) => {
    // 连接到数据库
    connection.connect();

    // 执行查询
    connection.query(query, (error, results, fields) => {
      if (error) {
        // 如果查询出错，关闭连接并拒绝 Promise
        connection.end();
        return reject(error);
      }

      // 遍历查询结果
      const parsedResults = results.map((row) => {
        // 遍历每一行的字段
        for (const key in row) {
          if (typeof row[key] === "string") {
            try {
              // 尝试将字符串解析为 JSON
              const parsedValue = JSON.parse(row[key]);
              // 如果解析成功，则将字段值替换为解析后的对象
              row[key] = parsedValue;
            } catch (e) {
              // 如果解析失败，说明不是 JSON 字符串，忽略
            }
          }
        }
        return row; // 返回处理后的行
      });

      // 关闭数据库连接
      connection.end();

      // 返回处理后的结果
      resolve(parsedResults);
    });
  });
};

const handleExport = (
  data,
  filename = dayjs().format("tps_YYYYMMDD_HHmmss")
) => {
  try {
    // 创建一个新的工作簿
    const wb = XLSX.utils.book_new();

    // 将JSON数据转换为工作表
    const ws = XLSX.utils.json_to_sheet(data);

    // 将工作表添加到工作簿中，并命名为"Sheet1"
    XLSX.utils.book_append_sheet(wb, ws, "Sheet1");

    filePath = path.join(__dirname, `/output/${filename}.xlsx`);
    // 写入文件
    XLSX.writeFile(wb, filePath);

    console.log(`Excel文件已成功生成: ${filePath}`);
  } catch (error) {
    console.error("导出Excel时出错:", error);
  }
};

const sql =
  "SELECT `moment`,`content` FROM `2kt_flow_record_airports` WHERE `item` = 'ZUUU' ORDER BY `moment` ASC";

handleQuery(sql).then((res) => {
  // console.log(res);
  const dailyResults = [];
  res.forEach((row) => {
    const day = dayjs(row.moment);
    const date = day.format("YYYY-MM-DD");
    const dow = day.format("d");
    //
    if (
      dailyResults.length == 0 ||
      dailyResults[dailyResults.length - 1].date != date
    ) {
      dailyResults.push({ date, dow, val9: 0, val10: 0 });
    }
    let target = dailyResults[dailyResults.length - 1];
    //
    if (day.hour() == 9 && (day.minute() == 30 || day.minute() == 45)) {
      row.content.forEach((val) => (target.val9 += val));
    } else if (day.hour() == 10 && (day.minute() == 0 || day.minute() == 15)) {
      row.content.forEach((val) => (target.val10 += val));
    }
    //
  });
  // const dailyResultsTPS = dailyResults.filter(
  //   (cell) => cell.dow == 2 || cell.dow == 4
  // );
  // const dailyResultsNOR = dailyResults.filter(
  //   (cell) => cell.dow != 2 && cell.dow != 4
  // );
  // console.log(dailyResults);
  handleExport(dailyResults);
});
